<?php

/**
* bbs论坛 基础数据库访问
* 灵活配置的表名称,可随意更改,带有前缀,可避免冲突
* 定义的类常量可以被继承,也可以在未被实例化之前直接使用
*/
class m_bbs extends database
{
	/**
	 * 首先定义各个表的名称
	 */
	const userTable='bbs_user';  //用户表
	const postTable='bbs_post'; //帖子表
	const replyTable='bbs_reply'; //回复表
	const forumTable='bbs_forum'; //分类表
	const messageTable='bbs_message'; //消息表
	const configTable='bbs_config';

	/**
	 * 定义列表时每页的个数
	 */
	const pageSize=2;


	/**
	 * 定义帖子状态,删除,精华,置顶,锁定, 对应于 post的state
	 */
	const postDelete=0; //已删除
	const postLock=1; //锁定,关闭,禁止回复
	const postDefault=2; //默认
	const postMark=3; //精华帖子
	const postTop=4; //置顶
	const postMost=5; //全局置顶

	/**
	 * 定义帖子的阅读权限,阅读权限(游客,未验证会员,已验证会员,回复可见,版主,总版主,管理员，后台超级管理员)
	 * 管理员,和后台超级管理员 可登陆后台,也可登陆前台
	 * 游客和未验证会员不可发表帖子,也不可回复
	 * 帖子默认未验证会员可读,不可回复
	 */
	const readDefault=0; //帖子的默认阅读权限,全部,游客也可阅读
	const readLogin=1; //已登陆都可阅读
	const readReg=2; //已验证会员及以上
	const readModerator=3; //版主及以上


	/**
	 * 板块状态(0正常,-2删除,-1锁定) 
	 */

	const forumDefault=0;
	const forumLock=1; //锁定只能查看不能发帖,回复等,可制作帖子回收站
	const forumDelete=2;

	/**
	 * 用户的等级状态
	 */
	const userDefault=0; //普通会员,默认
	const userAllModerator=1; //总版主
	const userManager=2; //管理员
	const userSuper=3; //超级管理员

	/**
	 * 用户的可用状态
	 */
	const ustateDelete=0; //已删除,不可恢复,不删除其关联数据,不解除邮箱占用
	const ustateFreeze=1;  //冻结,不能登录,可以解冻,占用邮箱
	const ustateDefault=2; //默认,注册未验证
	const ustateReg=3; //注册已验证

	/**
	 * 身份等级,9等级,根据user.grade积分取得.
	 * 
	 * 帖子加精+1金币,帖子锁定-1 金币,帖子删除-1金币
	 * 积分代表活跃度 每日登陆+1积分,每日发表帖子+1积分,每日回复+1积分
	 */
	function __construct()
	{

	}

	public static $level=array(
			array('玄铁剑客',1),
			array('云纹剑儒',20),
			array('青钢剑侠',60),
			array('望舒剑灵',120),
			array('盘龙剑王',200),
			array('羲和剑魔',300),
			array('太乙剑痴',420),
			array('无尘剑仙',560),
			array('逍遥剑圣',720)
		); //九等级积分表	

	/**
	 * 等级计算,根据积分获取称号,以及下一级所差积分,积分只增不减
	 */
	function userGrade($point=1)
	{
		$map=array_reverse(self::$level);
		foreach ($map as $k => $v)
		{
			if($point>=$v[1])
			{
				$data['currentGrade']=$map[$k][0];
				$next=max($k-1,0);
				$data['nextGrade']=$map[$next];
				$data['point']=max($map[$next][1]-$point,0);
				return $data;
				break;
			}
		}

	}

	///////////////////////////用户相关///////////////////////////////
	/**
	 * 判断指定ID用户是否存在
	 */
	function userExist($id)
	{
		$ret=$this->selectById(self::userTable,$id,'id');
		return $ret==false?false:true;
	}
	/**
	 * 指定用户名是否存在
	 */
	function userExistByName($name)
	{
		$ret=$this->selectWhere(self::userTable,array('name'=>$name),' limit 1 ');
		return $ret==false?false:true;

	}
	/**
	 * 指定邮箱的用户是否存在
	 */
	function userExistByEmail($email)
	{
		$ret=$this->selectWhere(self::userTable,array('email'=>$email),' limit 1 ');
		return $ret==false?false:true;
	}
	/**
	 * 是否存在用户名或密码
	 */
	function userExistByNameOrEmail($name,$email)
	{
		$exist=$this->userExistByName($name);
		if(!$exist)
		{
			return $this->userExistByEmail($email);
		}
		return true;
	}

	/**
	 * 修改密码 
	 */	
	function updateUserPass($id,$pass)
	{
		$ret=$this->updateById(self::userTable,$id,array('pass'=>$pass));
		return $ret;
	}
	function updateUserPassByName($name,$pass)
	{
		$ret=$this->updateWhere(self::userTable,array('name'=>$name),array('pass'=>$pass));
		return $ret;
	}
	function updateUserPassByEmail($email,$pass)
	{
		$ret=$this->updateWhere(self::userTable,array('email'=>$email),array('pass'=>$pass));
	}

	/**
	 * 更新用户信息
	 */
	function updateUserInfo($id,$arr)
	{
		$ret=$this->updateById(self::userTable,$id,$arr);
	}
	/**
	 * 用户列表
	 */
	function userList($page=1)
	{
		$ret=$this->getList(self::userTable,$page);
		return $ret;
	}
	/**
	 * 获取可用用户列表
	 */
	function userListAvailable($page=1,$per=10)
	{
		$offset=max(($page-1)*$per,0);
		$sql="select * from ".self::userTable." where state > 0 order by id desc limit $offset,$per ";
		$ret['list']=$this->getData($sql);
		$ret['page']=10;
		return $ret;
	}
	function userListWhere($page=1,$where)
	{
		$ret=$this->getList(self::userTable,$page,$where);
		return $ret;
	}
	/**
	 * 删除一个用户
	 */
	function deleteUser($id)
	{
		$data=array('state'=>self::ustateDelete);
		return $this->updateById(self::userTable,$id,$data);
	}
	function deleteUserByEmail($email)
	{
		return $this->updateWhere(self::userTable,array('email'=>$email),array('state'=>self::ustateDelete));
	}
	/**
	 * 冻结一个用户
	 */
	function freezeUser($id)
	{
		$data=array('state'=>self::ustateFreeze);
		return $this->updateById(self::userTable,$id,$data);
	}
	/**
	 * 添加一个用户
	 */
	function createUser($name,$pass,$email)
	{
		$data['name']=$name;
		$data['pass']=$pass;
		$data['email']=$email;
		$data['logtime']=$data['regtime']=time();
		return $this->insertData(self::userTable,$data);
	}
	/**
	 * 获得一个用户信息
	 */
	function getUserInfo($id)
	{
		return $this->selectById(self::userTable,$id);
	}
	/**
	 * 获取当前登录用户的信息,利用session作为缓存
	 */
	function getCurrentUserInfo($sessionKey='USERID')
	{
		$key='CURRENTUSERINFO';
		$info=session($key);
		if($info)
		{
			return json_decode($info,true);
		}
		else
		{
			$userId=session($sessionKey);
			$data=$this->getUserInfo($userId);
			session($key,$data);
			return $data;
		}

	}
	/**
	 * 登陆时验证,按邮箱和密码
	 */
	function loginUser($email,$pass)
	{
		return $this->selectWhere(self::userTable,array('email'=>$email,'pass'=>$pass));
	}
	/**
	 * 用户登陆,按用户名和密码
	 */
	function loginUserByName($name,$pass)
	{
		$ret=$this->selectWhere(self::userTable,array('name'=>$name,'pass'=>$pass));
		if($ret)
		{
			$this->updateUserTime($ret[0]['id']);
		}
		return $ret;
	}
	/**
	 * 更新用户活动时间
	 */
	function updateUserTime($uid)
	{
		return $this->updateById(self::userTable,$uid,array('logtime'=>time()));
	}
	/**
	 * 触发更新在线信息事件
	 */
	function updateOnlineEvent()
	{
		$sql="UPDATE `".self::configTable."` SET value=(SELECT COUNT(1) FROM `".self::userTable."` WHERE unix_timestamp(now())-logtime <3600 ) WHERE `key`='currentonline' ";
		exit($sql);
	}
	/**
	 *  统计在线数量,用户登陆,发帖,回帖均更新最后活动时间
	 *	统计活动时间在1小时以内的
	 */
	function onlineCount()
	{
		$sql="SELECT COUNT(1) FROM `".self::userTable."` WHERE unix_timestamp(now())-logtime <3600 ";
		return $this->getVar($sql);
	}
	/////////////////////////////////////板块相关/////////////////////////////
	/**
	 * 获得所有板块以及其信息,显示100条,不分页
	 */
	function getforumList()
	{
		$ret=$this->getList(self::forumTable,1,null,'id','asc',100);
		return $ret['list'];
	}
	/**
	 * 获取板块的信息
	 */
	function getForumInfo($fid)
	{
		return $this->selectById(self::forumTable,$fid);
	}

	////////////////////////////////////帖子相关//////////////////////////////
	/**
	 * 获取指定板块的最新帖子,包含分页
	 */
	function getTopicList($fid,$page=1)
	{
		$column='p.id post_id ,forum_id, f.name forum_name,title post_title,p.create_time post_create_time,p.active_time post_active_time,replies,views,p.state,p.right,p.author author_id ,u.name author_name,p.replyer replyer_id,(select name from `'.self::userTable.'` where id=replyer) as replyer_name';
		$page=max($page,1);
		$pageSize=self::pageSize;
		$offset=max(intval(($page-1)*$pageSize),0);
		$sql="SELECT {$column} FROM `".self::postTable."` p LEFT JOIN `".self::userTable."` u ON p.author=u.id  LEFT JOIN `".self::forumTable."` f ON forum_id=f.id WHERE p.state>='".self::postLock."' and forum_id='{$fid}' ORDER BY active_time desc limit {$offset},{$pageSize}  ";
		$data['list']=$this->getData($sql);
		$sql="SELECT COUNT(1) FROM `".self::postTable."` WHERE state>='".self::postLock."' and forum_id='{$fid}'";
		$data['page']=ceil($this->getVar($sql)/$pageSize);
		return $data;
	}
	/**
	 * 获取全局置顶和本版置顶的帖子
	 */
	function getPostTop($fid)
	{
		$column='p.id post_id ,forum_id, f.name forum_name,title post_title,p.create_time post_create_time,p.active_time post_active_time,replies,views,p.state,p.right,p.author author_id ,u.name author_name,p.replyer replyer_id,(select name from `'.self::userTable.'` where id=replyer) as replyer_name';
		$sql="SELECT {$column} FROM `".self::postTable."` p LEFT JOIN `".self::userTable."` u ON p.author=u.id LEFT JOIN `".self::forumTable."` f ON forum_id=f.id WHERE p.state='".self::postTop."'  and forum_id='{$fid}' or p.state>'".self::postTop."' order by p.state desc ";
		$ret=$this->getData($sql);
		return $ret;
	}
	/**
	 * 获取精华帖子
	 */
	function getPostMark($fid=null,$page=1,$pageSize=20)
	{
		$where=$fid?array('forum_id'=>$fid,'state'=>self::postMark):array('state'=>self::postMark);
		return $this->getList(self::postTable,$page,$where,'active_time','desc',$pageSize);
	}
	/**
	 * 帖子阅读+1
	 */
	function addPostViews($id)
	{
		return $this->incrById(self::postTable,'views',$id); 
	}
	/**
	 * 回复数+1
	 */
	function addPostReplies($id)
	{
		return $this->incrById(self::postTable,'replies',$id); 
	}
	/**
	 * 发布一个帖子
	 * 可以选择发布的板块
	 */
	function commitPost($data)
	{
		return $this->insertData(self::postTable,$data);
	}
	/**
	 * 更新一个帖子
	 * 可以移动,删除,加精,置顶等相关
	 */
	function updatePost($id,$data)
	{

		return $this->updateById(self::postTable,$id,$data);

	}
	/**
	 * 全局最新帖子列表或者指定最新
	 */
	function getNewPostList($forumId=null,$page=1,$num=50)
	{
		$offset=($page-1)*$num;
		$offset=$offset<0?0:$offset;
		$column='p.id post_id ,forum_id, f.name forum_name,title post_title,p.create_time post_create_time,p.active_time post_active_time,replies,views,p.state,p.right,p.author author_id ,u.name author_name,p.replyer replyer_id,(select name from `'.self::userTable.'` where id=replyer) as replyer_name';
		if($forumId)
		{
			$sql="SELECT {$column} FROM `".self::postTable."` p left join `".self::userTable."` u on p.author=u.id  LEFT JOIN `".self::forumTable."` f ON forum_id=f.id  WHERE forum_id='{$forumId}' and p.state >='".self::postLock."' ORDER BY active_time desc limit {$offset},{$num}";
			$ret['list']=$this->getData($sql);
			$sql="SELECT COUNT(1) FROM `".self::postTable."` p WHERE forum_id='{$forumId}' and p.state >='".self::postLock."' ";
			$ret['page']=ceil($this->getVar($sql)/$num);
			return $ret;
		}
		else
		{
			$sql="SELECT {$column} FROM `".self::postTable."` p left join `".self::userTable."` u on p.author=u.id  LEFT JOIN `".self::forumTable."` f ON forum_id=f.id  WHERE  p.state >='".self::postLock."' ORDER BY active_time desc limit {$offset},{$num}";
			$ret['list']=$this->getData($sql);
			$sql="SELECT COUNT(1) FROM `".self::postTable."` p WHERE  p.state >='".self::postLock."' ";
			$ret['page']=ceil($this->getVar($sql)/$num);
			return $ret;
		}
	}
	/**
	 * 获取一篇帖子的内容及作者信息
	 */
	function getPostContent($tid)
	{
		$sql="select p.id post_id,author author_id,name author_name,title post_title,text post_text,create_time,replies,views,regtime,logtime,level,grade,money from `".self::postTable."` p left join  `".self::userTable."` u on p.author=u.id  where p.id='{$tid}'";
		return $this->getLine($sql);
	}


	////////////////////////////////////////////帖子内容,主题,回复,普通搜索//////////////////////////////

	/**
	 * 按帖子主题搜索
	 */
	function searchByTitle($title)
	{
		$data=$this->searchByColumn(self::postTable,'title',$title,'id,title,text,create_time,active_time,views,replies');
		foreach ($data as $key => $post)
		{
			$data[$key]['text']=mb_substr(trim(strip_tags($post['text'])),0,200,'utf-8');
		}
		return $data;
	}
	/**
	 * 按正文和回复搜索
	 */
	function searchByContent($text)
	{
		$sql="select p.id post_id,p.title post_title, p.text post_text from `".self::postTable."` p left join `".self::replyTable."` r on p.id=r.post_id  where p.text like '%{$text}%' or r.text like '%{$text}%' group by p.id limit 50";
		$data=$this->getData($sql);
		foreach ($data as $key => $post)
		{
			$data[$key]['post_text']=mb_substr(trim(strip_tags($post['post_text'])),0,200,'utf-8');
		}
		return $data;
	}

	/**
	 * 按用户名搜索用户
	 */
	function searchByUsername($username)
	{
		return $this->selectByColumn(self::userTable,'name',$username);
	}
	/**
	 * 按邮箱搜索用户
	 */
	function searchByEmail($email)
	{
		return $this->selectByColumn(self::userTable,'email',$email);
	}



	//////////////////////////////////评论及回复帖子相关////////////////////////////
	/**
	 * 获取一篇帖子的评论内容以及评论人等信息
	 */
	function getTheReplies($tid)
	{
		$sql="select r.id,r.user_id reply_user_id ,name reply_user_name,text reply_text , reply_time ,regtime,logtime,grade,level,money  from `".self::replyTable."` r left join `".self::userTable."` u on user_id=u.id  where post_id='{$tid}' order by reply_time desc";
		return $this->getData($sql);

	}
	/**
	 * 回复一个帖子
	 * 事先确定回复的帖子ID
	 * 输入用户的ID,时间,回复内容等
	 * 同时更新回复者的活动时间
	 */
	function replyPost($data)
	{
		$this->updateUserTime($data['user_id']);
		return $this->insertData(self::replyTable,$data);
	}
	/**
	 * 删除一个回复
	 */
	function deleteReply($id)
	{
		return $this->deleteById(self::replyTable,$id);
	}
	/**
	 * 修改一个回复
	 */
	function updateReply($id,$data)
	{
		return $this->updateById(self::replyTable,$id,$data);
	}

	/**
	 * 昨日贴数,今日帖子数,共计数,会员数,新会员
	 */
	function statsInfo()
	{

		$countInfo=$this->countInfo();

		$postMark=$this->getPostMark();

		$data['userCount']=$countInfo['user_num'];
		$data['postCount']=$countInfo['post_num']+$countInfo['reply_num'];
		$data['onlineCount']=$countInfo['online_num'];
		$data['latestUser']=$this->latestUser();//最近的一个会员
		$data['latestPost']=$this->latestPost();
		$data['dateCount']=$this->datePostCount();
		$data['postMark']=$postMark['list'];
		return $data;
	}
	/**
	 * 用户数,主题数,回复数,分类数,在线用户数
	 */
	function countInfo()
	{
		$sql="select (select count(1) from `".self::userTable."`) as user_num , (SELECT COUNT(1) FROM `".self::userTable."` WHERE unix_timestamp(now())-logtime <3600 ) as online_num, (select count(1) from `".self::postTable."`) as post_num ,(select count(1) from `".self::replyTable."`) as reply_num,(select count(1) from `".self::forumTable."`) as forum_num ";
		return $this->getLine($sql);
	}
	function replyCount()
	{
		return $this->count(self::replyTable);
	}
	function userCount()
	{
		return $this->count(self::userTable);
	}
	function postCount()
	{
		return $this->count(self::postTable);
	}
	function latestUser()
	{
		$ret=$this->getList(self::userTable,1,null,'id','desc',1);
		return isset($ret['list'][0])?$ret['list'][0]:array();
	}
	/**
	 * 最新主题,简略信息
	 */
	function latestPost($forumId=null,$num=10)
	{
		if($forumId)
		{
			$where=array('forum_id'=>$forumId);
			return $this->selectWhere(self::postTable,$where,"ORDER BY active_time DESC LIMIT {$num}");
		}
		else
		{
			$where=null;
			return $this->selectWhere(self::postTable,$where,"ORDER BY active_time DESC LIMIT {$num}");
		}

	}
	
	/**
	 * 今天,昨天的总帖子数,是主题和回复的和
	 */
	function datePostCount($all=false)
	{
		$today=strtotime(date('Y-m-d'));
		$yestoday=$today-86400;
		$sql="select (select count(1) from `".self::postTable."` where create_time > {$today} ) as t_post_num ,(select count(1) from `".self::replyTable."` where reply_time > {$today} ) as t_reply_num ,(select count(1) from `".self::postTable."` where create_time > {$yestoday}  and create_time <= {$today} ) as y_post_num ,(select count(1) from `".self::replyTable."` where reply_time > {$yestoday} and  reply_time <= {$today}) as y_reply_num";
		$ret=$this->getLine($sql);
		if($all)return $ret;
		$data=array('today'=>$ret['t_post_num']+$ret['t_reply_num'],'yestoday'=>$ret['y_post_num']+$ret['y_reply_num']);
		return $data;
	}
	/**
	 * 返回各版块主题数,回复数,帖子数,今日主题数
	 */
	function forumPosts()
	{
		$today=strtotime(date('Y-m-d'));
		$sql="SELECT COUNT(1) num,forum_id FROM `".self::postTable."` WHERE create_time > {$today} GROUP BY forum_id";
		$todayList=$this->getData($sql);
		$sql="SELECT forum_id,COUNT(1) post_num FROM `".self::postTable."`  GROUP BY forum_id ";
		$ret=$this->getData($sql); //各版块主题数
		$sql="SELECT forum_id ,COUNT(1) reply_num FROM `".self::replyTable."` r ,`".self::postTable."` p where r.post_id=p.id GROUP BY p.forum_id";
		$res=$this->getData($sql);
		foreach ($ret as $value)
		{
			$out[$value['forum_id']]['post_num']=$value['post_num'];
		}
		foreach ($todayList as $value)
		{
			$out[$value['forum_id']]['today_num']=$value['num'];
		}
		foreach ($res as $value)
		{
			$out[$value['forum_id']]['reply_num']=$value['reply_num'];
			$out[$value['forum_id']]['post_num']=isset($out[$value['forum_id']]['post_num'])?$out[$value['forum_id']]['post_num']:0;
			$out[$value['forum_id']]['total_num']=$value['reply_num']+$out[$value['forum_id']]['post_num'];
		}
		// var_dump($out); die;
		return $out;
	}
	///////////////////////////////////////主页信息相关/////////////////////////////////////

	/**
	 * 获取含有图片的帖子,以及其图片地址
	 *
	 */
	function getImgPost($forumId=null,$num=20)
	{
		$regex='http:\/\/.+\.(jpg|png|gif|jpeg)';
		if($forumId)
		{
			$sql="select id,title,text from `".self::postTable."` where forum_id='{$forumId}' and text regexp '{$regex}' order by id desc limit {$num}";
		}
		else
		{
			$sql="select id,title,text from `".self::postTable."` where text regexp '{$regex}' order by id desc limit {$num}";
		}
		$data=$this->getData($sql);
		$pattern='/http:\/\/[\w\/\.\?\+-=&!@]+?\.(jpg|png|gif|jpeg)/';//第二次过滤条件,可以指定自己的图片服务器,防止
		foreach ($data as $item)
		{
			if(preg_match($pattern, $item['text'],$match))
			{
				$item['text']=$match[0];
				$item['title']=strip_tags(mb_substr($item['title'],0,14,'utf-8'));
				$ret[]=$item;
			}
		}
		return $ret;

	}

	/**
	 * 获取首页推荐帖子
	 */
	function getIndexPost($num=10)
	{

	}
	/**
	 * 获取所有节点信息
	 */
	function getAllNode()
	{

	}
	
	///////////////////////////////即时消息相关////////////////////////////////
	/**
	 * 发送消息
	 * 提供发送者,接受者,发送正文,发送时间,查看时间
	 */
	function addMsg($data)
	{
		return $this->insertData(self::messageTable,$data);
	}
	/**
	 * 修改一个消息
	 */
	function updateMsg($id,$data)
	{
		return $this->updateById(self::messageTable,$id,$data);

	}
	/**
	 * 获取未读消息
	 */
	function unreadMsg($id)
	{
		return $this->selectWhere(self::messageTable,array('id'=>$id,'view_time'=>null));
	}

	//////////////////////////////////////admin 数据库映射操作/////////////////////////////////////////

	

	/**
	 * 四大操作
	 */
	function adminSelect($table,$page)
	{
		return $this->getList($table,$page);
	}
	function adminUpdate()
	{

	}
	function adminDelete()
	{

	}
	function adminInsert()
	{

	}

	/**
	 * 实际删除一个数据库记录
	 */
	function realDeleteById($table,$id)
	{
		$sql="delete from {$table} where id = '{$id}' ";
		return $this->runSql($sql);
	}
}